IF OBJECT_ID('dbo.RptAdvancedAccountsByDept') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAdvancedAccountsByDept
    IF OBJECT_ID('dbo.RptAdvancedAccountsByDept') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAdvancedAccountsByDept >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAdvancedAccountsByDept >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAdvancedAccountsByDept
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 6/19/2007
--
-- Description: Source of Report, "AdvancedAccountsByDeptReport"
--                  In Insight DB Reporting Services
--
--   Advance Accounts by Department
--   Report Description
--        This report documents those fund numbers that were setup in advance of 
--        an actual notice of award being received.  It allows us to proactively monitor 
--        and follow-up with the sponsor on the status of the award notice.
--
--
-- Parameters:  
--     @StartDate                         
--     @EndDate                           
--     @Institution                       
--     @Department                        
--     @PrincipalInvestigator                        
--     @ProposalStatus                    
--     @Sponsor                           
--     @Mechanism                         
--     @RMCaseMgr                         
--     @BudgetPeriodStatus                     
--     @domainUserId                      
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptAdvancedAccountsByDept()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/19/07     KC        Created
-- 7/26/07     KC        Changed Naming Conventions, readied for QA
-- 7/20/07     KC        Added PS.stat_type = 1 to dbo.InfoEdPropStat JOIN
-- 8/1/07      KC        Added QA Feedback, BudgetDates fixes
-- 8/6/2007    KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
-- 8/23/07     KC        Fixed BudgetLogic
-- 8/31/2007   KC        Changed to use new tables, AgrAgreement_Info AND
--                       AgrAgreement_Budget
-- 9/6/07      KC        Changes due to new BudgetView (using BudgetFct table)
-- 9/18/07     KC        Added @PreAwardManager as Param. Fixed SponsorAgreementID
-- 9/21/07     KC        Added @Unit
-- 9/25/07     KC        Used ID's for Inst,Dept,Unit
-- 9/28/07     KC        Added space at beginning of PreAward and PostAward
--                       for sorting. 
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptAdvancedAccountsByDept
     (
     @StartDate                         DATETIME = NULL,  
     @EndDate                           DATETIME = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL,             
     @Sponsor                           VARCHAR(100) = NULL,
     @ProposalStatus                    VARCHAR(100) = NULL,
     @PreAwardManager                   VARCHAR(100) = NULL,
     @PostAwardManager                  VARCHAR(100) = NULL,
     @FundNumber                        VARCHAR(100) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT
               
   -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @ProposalStatus IN ('ALL','','0') SET @ProposalStatus = NULL
     IF @PreAwardManager IN ('ALL','','0') SET @PreAwardManager = NULL
     IF @PostAwardManager IN ('ALL','','0') SET @PostAwardManager = NULL
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Result Set
     SELECT    AI.FolderNumber AS ProposalNumber,        
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.ProposalTitle AS ProposalTitle, 
               AI.AgreementType AS AgreementType,
               AI.TransactionType AS ProposalType,
               AI.Institution AS Institution,
               AI.Department AS Department,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS UnitCode,
               AI.SponsorName AS SponsorName,        
               AI.SponsorAgreementID AS SponsorNumber,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               PROJ.ProjectDirectCost AS DirectCostforProjectPeriod,
               BUDG.BudgetDirectCost AS DirectCostforBudgetPeriod,
               PROJ.ProjectIndirectCost AS FAforProjectPeriod,
               BUDG.BudgetIndirectCost AS FAforBudgetPeriod,
               PROJ.IndirectCostRate AS ProjectIndirectCostRate,
               BUDG.IndirectCostRate AS BudgetIndirectCostRate,
               BUDG.BudgetViewType AS BudgetPeriodStatus,
               AI.PostAwardManagerCode AS PostAwardManager,
               ISNULL(AI.PostAwardManagerName,' Post Award Manager Not Assigned') AS PostAwardManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,' Pre Award Manager Not Assigned') AS PreAwardManagerName,
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,  
               AI.BackupAccountNumber AS BackupAccountNumber,
               AI.ProposalStatus AS ProposalStatus,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.SponsorId AS SponsorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreemendId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN 

               -- ProjectDirectCosts have to be summed from the Budget tables
               -- Over the life of the Agreement (Project)
               dbo.vwAgreementProjectBudgets PROJ ON AI.AgreementId = PROJ.AgreementId LEFT OUTER JOIN

               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN
               
               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
               
     WHERE     (@StartDate IS NULL OR AI.ProjectStartDate BETWEEN @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND
               (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor ))) AND
               (@ProposalStatus IS NULL OR AI.ProposalStatus IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @ProposalStatus ))) AND
               (@PreAwardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PreAwardManager ))) AND
               (@PostAwardManager IS NULL OR AI.PostAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PostAwardManager ))) AND
               (@FundNumber IS NULL OR BUDG.FundNumber IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @FundNumber ))) 
               
               --  And Advanced Fund has one of these two set on the InfoEdAwards table
               --  Which has been set on the AgrAgreement_Info table as well.
                AND (AI.AdvanceFundYN = 'Yes' OR AI.BackupAccountNumber NOT IN (NULL,''))
     ORDER BY AI.SponsorName, AI.Institution, AI.Department


     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
    
END
go

IF OBJECT_ID('dbo.RptAdvancedAccountsByDept') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAdvancedAccountsByDept >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAdvancedAccountsByDept >>>'
go

GRANT EXECUTE ON dbo.RptAdvancedAccountsByDept TO InsightReport,Insight
go
